{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "b3bdfe78-a28d-4d66-aa49-05a49197ec7f",
   "metadata": {},
   "source": [
    "# 导包"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "09a98d08",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-03-22T06:40:19.311556Z",
     "iopub.status.busy": "2024-03-22T06:40:19.311212Z",
     "iopub.status.idle": "2024-03-22T06:40:19.438340Z",
     "shell.execute_reply": "2024-03-22T06:40:19.437744Z",
     "shell.execute_reply.started": "2024-03-22T06:40:19.311538Z"
    },
    "ExecuteTime": {
     "end_time": "2024-03-28T12:12:22.192912Z",
     "start_time": "2024-03-28T12:12:21.367896Z"
    }
   },
   "outputs": [],
   "source": [
    "import sys\n",
    "sys.path.append(\"/home/loong/jupyter\")\n",
    "import common_utils\n",
    "from common_utils import *"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b253daac",
   "metadata": {},
   "source": [
    "# 加载数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "07be2fdc-57c2-4111-9dec-fa4008210ea1",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-03-22T06:40:38.090550Z",
     "iopub.status.busy": "2024-03-22T06:40:38.089882Z",
     "iopub.status.idle": "2024-03-22T06:40:39.238567Z",
     "shell.execute_reply": "2024-03-22T06:40:39.238028Z",
     "shell.execute_reply.started": "2024-03-22T06:40:38.090524Z"
    },
    "ExecuteTime": {
     "end_time": "2024-03-28T12:12:24.174678Z",
     "start_time": "2024-03-28T12:12:22.692428Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "load 294 file,data shape (37254, 26)\n",
      "(37254, 26)\n"
     ]
    }
   ],
   "source": [
    "file_paths = data_of_dir(\"raw_012_am240327\",['_1_','_2_']) # type: ignore\n",
    "raw_df = batch_load_data(file_paths)\n",
    "print(raw_df.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "cf66fa3f-ad12-4397-9504-4882e78fc776",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-03-28T12:12:25.360704Z",
     "start_time": "2024-03-28T12:12:24.176426Z"
    }
   },
   "outputs": [],
   "source": [
    "app_classification_df = pd.read_excel('ios_app_rank_mx20240125.xlsx')\n",
    "def preprocess_df(df):\n",
    "    # 列表中的列名\n",
    "    columns_to_preprocess = [\"app_package\", \"classification\", \"classification_sub\", \"developer\", \"fee_status\"]\n",
    "\n",
    "    # 对每一列进行预处理\n",
    "    for column in columns_to_preprocess:\n",
    "        df[column] = df[column].str.lower().str.strip().str.replace(' ', '')\n",
    "\n",
    "    return df\n",
    "app_classification_df = preprocess_df(app_classification_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "aab14902-88dc-4b2f-b161-ae253a841b63",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-03-28T12:12:41.193140Z",
     "start_time": "2024-03-28T12:12:25.987878Z"
    }
   },
   "outputs": [],
   "source": [
    "applit_data_column='applist_data'\n",
    "id_column='app_order_id'\n",
    "end_time_column='sms_upload_time'\n",
    "user_apps= parse_json_data(raw_df,applit_data_column,id_column,end_time_column)\n",
    "\n",
    "user_apps['app_name'] = user_apps['app_name'].str.lower().str.strip()\n",
    "user_apps['app_package'] = user_apps['app_package'].str.lower().str.strip().str.replace(' ', '')\n",
    "user_apps['isSystem']  = user_apps['app_package'].map(lambda x :1 if 'com.apple' in x else 0 )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "692620dd-8cbb-40f7-aa0e-23d5c4df21f1",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-03-28T12:12:42.912343Z",
     "start_time": "2024-03-28T12:12:41.194867Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(37254, 26)\n",
      "(35739, 1)\n"
     ]
    }
   ],
   "source": [
    "# 假设user_apps和app_classification_df是你的两个DataFrame\n",
    "user_applist_df = pd.merge(user_apps, app_classification_df[['app_package','classification', 'classification_sub', 'developer', 'fee_status']], on='app_package', how='left')\n",
    "# 将 'classification', 'classification_sub', 'developer', 和 'fee_status' 这四个字段中的 NaN 值替换为 'other'\n",
    "user_applist_df[['classification', 'classification_sub', 'developer', 'fee_status']] = \\\n",
    "    user_applist_df[['classification', 'classification_sub', 'developer', 'fee_status']].fillna('other')\n",
    "\n",
    "applist_variable_df = user_applist_df[[id_column]].drop_duplicates(subset=[id_column]) # type: ignore\n",
    "print(raw_df.shape)\n",
    "print(applist_variable_df.shape)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bc58dfc6",
   "metadata": {},
   "source": [
    "# 基于app分类特征"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "0d2cc955",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-03-28T12:12:48.138169Z",
     "start_time": "2024-03-28T12:12:48.135772Z"
    }
   },
   "outputs": [],
   "source": [
    "classification = ['other', 'financial', 'entertainment', 'tools', 'sports','shopping', 'life', 'tourism', 'business', 'music']\n",
    "classification_sub = ['other', 'wallet', 'cashloan', 'bnpl', 'bank', 'stock', 'credit','creditcard', 'web3', 'accounting', 'caculation', 'insurance']\n",
    "id_column='app_order_id'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "fb748dae",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-03-28T12:12:50.999286Z",
     "start_time": "2024-03-28T12:12:48.841802Z"
    }
   },
   "outputs": [],
   "source": [
    "unique_app_package_count = user_applist_df.groupby(id_column)['app_package'].nunique()\n",
    "\n",
    "# 将结果添加到 'applist_variable_df' 中\n",
    "applist_variable_df['unique_app_package_count'] = applist_variable_df[id_column].map(\n",
    "    unique_app_package_count).fillna(0)\n",
    "\n",
    "# 计算 'user_applist_df' 中每个 'user_id' 当 'isSystem' 等于1时对应的不同 'app_package' 的数量\n",
    "system_app_package_count = user_applist_df[user_applist_df['isSystem'] == 1].groupby(id_column)[\n",
    "    'app_package'].nunique()\n",
    "\n",
    "# 将结果添加到 'applist_variable_df' 中\n",
    "applist_variable_df['system_app_package_count'] = applist_variable_df[id_column].map(\n",
    "    system_app_package_count).fillna(0)\n",
    "\n",
    "# 计算 'user_applist_df' 中每个 'user_id' 当 'isSystem' 等于0时对应的不同 'app_package' 的数量\n",
    "non_system_app_package_count = user_applist_df[user_applist_df['isSystem'] == 0].groupby(id_column)[\n",
    "    'app_package'].nunique()\n",
    "\n",
    "# 将结果添加到 'applist_variable_df' 中\n",
    "applist_variable_df['non_system_app_package_count'] = applist_variable_df[id_column].map(\n",
    "    non_system_app_package_count).fillna(0)\n",
    "\n",
    "# 计算 'user_applist_df' 中每个 'user_id' 对应的全部不同 'app_package' 的数量\n",
    "total_app_package_count = user_applist_df.groupby(id_column)['app_package'].nunique()\n",
    "\n",
    "# 计算 'system_app_package_count' 占 'total_app_package_count' 的占比\n",
    "system_app_package_ratio = system_app_package_count / total_app_package_count\n",
    "\n",
    "# 将结果添加到 'applist_variable_df' 中\n",
    "applist_variable_df['system_app_package_ratio'] = applist_variable_df[id_column].map(\n",
    "    system_app_package_ratio).fillna(0)\n",
    "\n",
    "# 计算 'non_system_app_package_count' 占 'total_app_package_count' 的占比\n",
    "non_system_app_package_ratio = non_system_app_package_count / total_app_package_count\n",
    "\n",
    "# 将结果添加到 'applist_variable_df' 中\n",
    "applist_variable_df['non_system_app_package_ratio'] = applist_variable_df[id_column].map(\n",
    "    non_system_app_package_ratio).fillna(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "9977ab2c",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-03-28T12:13:09.192447Z",
     "start_time": "2024-03-28T12:13:02.754267Z"
    }
   },
   "outputs": [],
   "source": [
    "categories = ['other', 'financial', 'entertainment', 'tools', 'sports','shopping', 'life', 'tourism', 'business', 'music']\n",
    "\n",
    "# 计算 'user_applist_df' 中每个 'user_id' 对应的全部不同 'app_package' 的数量\n",
    "total_app_package_count = user_applist_df.groupby(id_column)['app_package'].nunique()\n",
    "\n",
    "# 计算 'isSystem' 等于0的 'app_package' 的总数量\n",
    "total_non_system_app_package_count = user_applist_df[user_applist_df['isSystem'] == 0].groupby(id_column)[\n",
    "    'app_package'].nunique()\n",
    "\n",
    "# 计算 'isSystem' 等于1的 'app_package' 的总数量\n",
    "total_system_app_package_count = user_applist_df[user_applist_df['isSystem'] == 1].groupby(id_column)[\n",
    "    'app_package'].nunique()\n",
    "\n",
    "for category in categories:\n",
    "    # 计算 'user_applist_df' 中每个 'user_id' 当 'classification' 在 'categories' 中时对应的不同 'app_package' 的数量\n",
    "    category_app_package_count = \\\n",
    "        user_applist_df[user_applist_df['classification'] == category].groupby(id_column)['app_package'].nunique()\n",
    "\n",
    "    # 将结果添加到 'applist_variable_df' 中\n",
    "    column_name = category.lower().replace(' ', '_') + '_app_package_count'\n",
    "    applist_variable_df[column_name] = applist_variable_df[id_column].map(category_app_package_count).fillna(0)\n",
    "\n",
    "    # 计算 'category_app_package_count' 占 'total_app_package_count' 的占比\n",
    "    category_app_package_ratio = category_app_package_count / total_app_package_count\n",
    "\n",
    "    # 将结果添加到 'applist_variable_df' 中，并填充缺失值为0\n",
    "    column_name = category.lower().replace(' ', '_') + '_app_package_ratio'\n",
    "    applist_variable_df[column_name] = applist_variable_df[id_column].map(category_app_package_ratio).fillna(0)\n",
    "\n",
    "    category_app_package_count = \\\n",
    "        user_applist_df[(user_applist_df['classification'] == category) & (user_applist_df['isSystem'] == 0)] \\\n",
    "            .groupby(id_column)['app_package'].nunique()\n",
    "\n",
    "    # 计算 'category_app_package_count' 占 'total_non_system_app_package_count' 的占比\n",
    "    category_app_package_ratio = category_app_package_count / total_non_system_app_package_count\n",
    "\n",
    "    # 将结果添加到 'applist_variable_df' 中，并填充缺失值为0\n",
    "    column_name = category.lower().replace(' ', '_') + '_non_system_app_package_ratio'\n",
    "    applist_variable_df[column_name] = applist_variable_df[id_column].map(category_app_package_ratio).fillna(0)\n",
    "\n",
    "    category_app_package_count = \\\n",
    "        user_applist_df[(user_applist_df['classification'] == category) & (user_applist_df['isSystem'] == 1)] \\\n",
    "            .groupby(id_column)['app_package'].nunique()\n",
    "\n",
    "    # 计算 'category_app_package_count' 占 'total_non_system_app_package_count' 的占比\n",
    "    category_app_package_ratio = category_app_package_count / total_system_app_package_count\n",
    "\n",
    "    # 将结果添加到 'applist_variable_df' 中，并填充缺失值为0\n",
    "    column_name = category.lower().replace(' ', '_') + '_system_app_package_ratio'\n",
    "    applist_variable_df[column_name] = applist_variable_df[id_column].map(category_app_package_ratio).fillna(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "4041d1e5",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-03-28T12:13:18.820983Z",
     "start_time": "2024-03-28T12:13:09.193566Z"
    }
   },
   "outputs": [],
   "source": [
    "# 定义新的子类别列表\n",
    "categories_sub =['other', 'wallet', 'cashloan', 'bnpl', 'bank', 'stock', 'credit','creditcard', 'web3', 'accounting', 'caculation', 'insurance']\n",
    "\n",
    "# 计算 'user_applist_df' 中每个 'user_id' 对应的全部不同 'app_package' 的数量\n",
    "total_app_package_count = user_applist_df.groupby(id_column)['app_package'].nunique()\n",
    "\n",
    "# 计算 'isSystem' 等于0的 'app_package' 的总数量\n",
    "total_non_system_app_package_count = user_applist_df[user_applist_df['isSystem'] == 0].groupby(id_column)[\n",
    "    'app_package'].nunique()\n",
    "\n",
    "# 计算 'isSystem' 等于1的 'app_package' 的总数量\n",
    "total_system_app_package_count = user_applist_df[user_applist_df['isSystem'] == 1].groupby(id_column)[\n",
    "    'app_package'].nunique()\n",
    "\n",
    "total_finance_app_package_count = \\\n",
    "    user_applist_df[user_applist_df['classification'] == 'finance'].groupby(id_column)[\n",
    "        'app_package'].nunique()\n",
    "\n",
    "for category in categories_sub:\n",
    "    # 计算 'user_applist_df' 中每个 'user_id' 当 'classification_sub' 在 'categories_sub' 中时对应的不同 'app_package' 的数量\n",
    "    category_sub_app_package_count = \\\n",
    "        user_applist_df[user_applist_df['classification_sub'] == category].groupby(id_column)[\n",
    "            'app_package'].nunique()\n",
    "\n",
    "    # 将结果添加到 'applist_variable_df' 中\n",
    "    column_name = category.lower().replace(' ', '_') + '_sub_app_package_count'\n",
    "    applist_variable_df[column_name] = applist_variable_df[id_column].map(category_sub_app_package_count).fillna(\n",
    "        0)\n",
    "\n",
    "    # 计算 'category_app_package_count' 占 'total_app_package_count' 的占比\n",
    "    category_app_package_ratio = category_sub_app_package_count / total_app_package_count\n",
    "\n",
    "    # 将结果添加到 'applist_variable_df' 中，并填充缺失值为0\n",
    "    column_name = category.lower().replace(' ', '_') + '_sub_app_package_ratio'\n",
    "    applist_variable_df[column_name] = applist_variable_df[id_column].map(category_app_package_ratio).fillna(0)\n",
    "\n",
    "    category_sub_app_package_count = \\\n",
    "        user_applist_df[(user_applist_df['classification_sub'] == category) & (user_applist_df['isSystem'] == 0)] \\\n",
    "            .groupby(id_column)['app_package'].nunique()\n",
    "\n",
    "    # 计算 'category_app_package_count' 占 'total_non_system_app_package_count' 的占比\n",
    "    category_app_package_ratio = category_sub_app_package_count / total_non_system_app_package_count\n",
    "\n",
    "    # 将结果添加到 'applist_variable_df' 中，并填充缺失值为0\n",
    "    column_name = category.lower().replace(' ', '_') + '_sub_non_system_app_package_ratio'\n",
    "    applist_variable_df[column_name] = applist_variable_df[id_column].map(category_app_package_ratio).fillna(0)\n",
    "\n",
    "    category_sub_app_package_count = \\\n",
    "        user_applist_df[(user_applist_df['classification_sub'] == category) & (user_applist_df['isSystem'] == 1)] \\\n",
    "            .groupby(id_column)['app_package'].nunique()\n",
    "\n",
    "    # 计算 'category_app_package_count' 占 'total_non_system_app_package_count' 的占比\n",
    "    category_app_package_ratio = category_sub_app_package_count / total_system_app_package_count\n",
    "\n",
    "    # 将结果添加到 'applist_variable_df' 中，并填充缺失值为0\n",
    "    column_name = category.lower().replace(' ', '_') + '_sub_system_app_package_ratio'\n",
    "    applist_variable_df[column_name] = applist_variable_df[id_column].map(category_app_package_ratio).fillna(0)\n",
    "\n",
    "    category_sub_app_package_count = \\\n",
    "        user_applist_df[\n",
    "            (user_applist_df['classification_sub'] == category) & (user_applist_df['classification'] == 'finance')] \\\n",
    "            .groupby(id_column)['app_package'].nunique()\n",
    "\n",
    "    # 计算 'category_app_package_count' 占 'total_finance_app_package_count' 的占比\n",
    "    category_app_package_ratio = category_sub_app_package_count / total_finance_app_package_count\n",
    "\n",
    "    # 将结果添加到 'applist_variable_df' 中，并填充缺失值为0\n",
    "    column_name = category.lower().replace(' ', '_') + '_sub_finance_app_package_ratio'\n",
    "    applist_variable_df[column_name] = applist_variable_df[id_column].map(category_app_package_ratio).fillna(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "fd01a18f",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-03-28T12:13:22.880389Z",
     "start_time": "2024-03-28T12:13:18.822477Z"
    }
   },
   "outputs": [],
   "source": [
    "fee_status_list = ['free', 'charge', 'other']\n",
    "\n",
    "# 计算 'user_applist_df' 中每个 'user_id' 对应的全部不同 'app_package' 的数量\n",
    "total_app_package_count = user_applist_df.groupby(id_column)['app_package'].nunique()\n",
    "\n",
    "# 计算 'isSystem' 等于0的 'app_package' 的总数量\n",
    "total_non_system_app_package_count = user_applist_df[user_applist_df['isSystem'] == 0].groupby(id_column)[\n",
    "    'app_package'].nunique()\n",
    "\n",
    "# 计算 'isSystem' 等于1的 'app_package' 的总数量\n",
    "total_system_app_package_count = user_applist_df[user_applist_df['isSystem'] == 1].groupby(id_column)[\n",
    "    'app_package'].nunique()\n",
    "\n",
    "for status in fee_status_list:\n",
    "    # 计算 'user_applist_df' 中每个 'user_id' 当 'fee_status' 为当前状态时对应的不同 'app_package' 的数量\n",
    "    status_app_package_count = user_applist_df[user_applist_df['fee_status'] == status].groupby(id_column)[\n",
    "        'app_package'].nunique()\n",
    "\n",
    "    # 将结果添加到 'applist_variable_df' 中\n",
    "    column_name = status + '_fee_app_package_count'\n",
    "    applist_variable_df[column_name] = applist_variable_df[id_column].map(status_app_package_count).fillna(0)\n",
    "\n",
    "    # 计算 'status_app_package_count' 占 'total_app_package_count' 的占比\n",
    "    status_app_package_ratio = status_app_package_count / total_app_package_count\n",
    "\n",
    "    # 将结果添加到 'applist_variable_df' 中\n",
    "    column_name = status + '_fee_app_package_ratio'\n",
    "    applist_variable_df[column_name] = applist_variable_df[id_column].map(status_app_package_ratio).fillna(0)\n",
    "\n",
    "    status_app_package_count = \\\n",
    "        user_applist_df[(user_applist_df['fee_status'] == status) & (user_applist_df['isSystem'] == 0)].groupby(\n",
    "            id_column)[\n",
    "            'app_package'].nunique()\n",
    "    # 计算 'status_app_package_count' 占 'isSystem' 等于0的 'app_package' 数量的占比\n",
    "    status_non_system_app_package_ratio = status_app_package_count / total_non_system_app_package_count\n",
    "\n",
    "    # 将结果添加到 'applist_variable_df' 中\n",
    "    column_name = status + '_fee_non_system_app_package_ratio'\n",
    "    applist_variable_df[column_name] = applist_variable_df[id_column].map(\n",
    "        status_non_system_app_package_ratio).fillna(0)\n",
    "\n",
    "    status_app_package_count = \\\n",
    "        user_applist_df[(user_applist_df['fee_status'] == status) & (user_applist_df['isSystem'] == 1)].groupby(\n",
    "            id_column)[\n",
    "            'app_package'].nunique()\n",
    "\n",
    "    # 计算 'status_app_package_count' 占 'isSystem' 等于1的 'app_package' 数量的占比\n",
    "    status_system_app_package_ratio = status_app_package_count / total_system_app_package_count\n",
    "\n",
    "    # 将结果添加到 'applist_variable_df' 中\n",
    "    column_name = status + '_fee_system_app_package_ratio'\n",
    "    applist_variable_df[column_name] = applist_variable_df[id_column].map(status_system_app_package_ratio).fillna(\n",
    "        0)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bc756770",
   "metadata": {},
   "source": [
    "# 基于risk_level 进行特征衍生"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "7f0e6097",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-03-28T12:13:25.130621Z",
     "start_time": "2024-03-28T12:13:22.881975Z"
    }
   },
   "outputs": [],
   "source": [
    "applicateion_level_df = pd.read_excel(\"app_r_level.xlsx\")\n",
    "user_applist_df = user_applist_df.merge(applicateion_level_df,how='left',on='app_package')\n",
    "user_applist_df['r_level']  = user_applist_df['r_level'].fillna('L0')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a3321be6",
   "metadata": {},
   "source": [
    "## 特征计算"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "9dce4511",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-03-28T12:13:30.616585Z",
     "start_time": "2024-03-28T12:13:25.131681Z"
    }
   },
   "outputs": [],
   "source": [
    "r_levels = ['L0','L1','L2','L3','L4']\n",
    "for r_level in r_levels :\n",
    "    # 计算 'user_applist_df' 中每个 'user_id' 当 'classification' 在 'categories' 中时对应的不同 'app_package' 的数量\n",
    "    category_app_package_count = \\\n",
    "        user_applist_df[user_applist_df['r_level'] == r_level].groupby(id_column)['app_package'].nunique()\n",
    "\n",
    "    # 将结果添加到 'applist_variable_df' 中\n",
    "    column_name =  f'{r_level}_r_level_app_package_count'\n",
    "    applist_variable_df[column_name] = applist_variable_df[id_column].map(category_app_package_count).fillna(0)\n",
    "\n",
    "    # 计算 'category_app_package_count' 占 'total_app_package_count' 的占比\n",
    "    category_app_package_ratio = category_app_package_count / total_app_package_count\n",
    "\n",
    "    # 将结果添加到 'applist_variable_df' 中，并填充缺失值为0\n",
    "    column_name =  f'{r_level}_r_level_app_package_ratio'\n",
    "    applist_variable_df[column_name] = applist_variable_df[id_column].map(category_app_package_ratio).fillna(0)\n",
    "\n",
    "    category_app_package_count = \\\n",
    "        user_applist_df[(user_applist_df['classification'] == r_level) & (user_applist_df['isSystem'] == 0)] \\\n",
    "            .groupby(id_column)['app_package'].nunique()\n",
    "\n",
    "    # 计算 'category_app_package_count' 占 'total_non_system_app_package_count' 的占比\n",
    "    category_app_package_ratio = category_app_package_count / total_non_system_app_package_count\n",
    "\n",
    "    # 将结果添加到 'applist_variable_df' 中，并填充缺失值为0\n",
    "    column_name =  f'{r_level}_r_level_non_system_app_package_ratio' # category.lower().replace(' ', '_') + '_non_system_app_package_ratio'\n",
    "    applist_variable_df[column_name] = applist_variable_df[id_column].map(category_app_package_ratio).fillna(0)\n",
    "\n",
    "    category_app_package_count = \\\n",
    "        user_applist_df[(user_applist_df['r_level'] == r_level) & (user_applist_df['isSystem'] == 1)] \\\n",
    "            .groupby(id_column)['app_package'].nunique()\n",
    "\n",
    "    # 计算 'category_app_package_count' 占 'total_non_system_app_package_count' 的占比\n",
    "    category_app_package_ratio = category_app_package_count / total_system_app_package_count\n",
    "\n",
    "    # 将结果添加到 'applist_variable_df' 中，并填充缺失值为0\n",
    "    column_name = f'{r_level}_r_level_system_app_package_ratio' #category.lower().replace(' ', '_') + '_system_app_package_ratio'\n",
    "    applist_variable_df[column_name] = applist_variable_df[id_column].map(category_app_package_ratio).fillna(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "c7bf3d0c",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-03-28T12:13:43.730626Z",
     "start_time": "2024-03-28T12:13:43.649424Z"
    }
   },
   "outputs": [],
   "source": [
    "applist_variable_df.to_parquet('applist_features240326.parquet',compression='zstd')"
   ]
  },
  {
   "cell_type": "code",
   "outputs": [],
   "source": [],
   "metadata": {
    "collapsed": false
   },
   "id": "95686652e0b5311c"
  }
 ],
 "metadata": {
  "kernelspec": {
   "name": "py311",
   "language": "python",
   "display_name": "py311"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.5"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  },
  "varInspector": {
   "cols": {
    "lenName": 16,
    "lenType": 16,
    "lenVar": 40
   },
   "kernels_config": {
    "python": {
     "delete_cmd_postfix": "",
     "delete_cmd_prefix": "del ",
     "library": "var_list.py",
     "varRefreshCmd": "print(var_dic_list())"
    },
    "r": {
     "delete_cmd_postfix": ") ",
     "delete_cmd_prefix": "rm(",
     "library": "var_list.r",
     "varRefreshCmd": "cat(var_dic_list()) "
    }
   },
   "types_to_exclude": [
    "module",
    "function",
    "builtin_function_or_method",
    "instance",
    "_Feature"
   ],
   "window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
